/*
--EXEC sp_purchase_order_audit_trail 3, 1000, '02/13/2005', '02/09/2005 ', 'wqw', 20, 30, 'admin', @revisionNumber OUT
DECLARE @revisionNumber INT
EXEC sp_purchase_order_audit_trail 827, N'admin', N'', 2, -1, N'', N'', N'', -1, -1, -1, -1, N'', -1, -1, -1, 4.030000000000000e+001, 1.190000000000000e+001, 1.490000000000000e+001, 1.190000000000000e+001, 0.000000000000000e+000, 4.920000000000000e+001, @revisionNumber OUT

BEGIN
DECLARE @revisionNumber INT
EXEC sp_purchase_order_audit_trail 524, 'SA', 'VNO-ASEM-1083-1', 0 , -1 , '04/06/2005   ', '03/29/2005' , 600, 55, 12, 14, 'status-1083-1', 0, 1, 1, @revisionNumber OUT
PRINT @revisionNumber
END
*/

ALTER      PROC sp_purchase_order_audit_trail( @purchaseOrder_id INT,
@user_id VARCHAR(50),
@vendorQuote VARCHAR(50),
@isLineItemInfo INT,
@poLineItem_ID INT,
@packageType VARCHAR(50),
@requestedDate VARCHAR(50),
@promisedDate VARCHAR(50),
@requestedQty INT,
@receivedQty INT,
@unitCost NUMERIC(18,2),
@extendedCost NUMERIC(18,2),
@status VARCHAR(50),
@cancel INT,
@financeHold INT,
@operationsHold INT,
@subTotal NUMERIC(18,2),
@freight NUMERIC(18,2),
@tradeDiscount NUMERIC(18,2),
@misc NUMERIC(18,2),
@tax NUMERIC(18,2),
@total NUMERIC(18,2),
@taxType VARCHAR(50),
@revisionNumber INT OUT
)
AS
BEGIN
DECLARE @prev_vendorQuote VARCHAR(50)
DECLARE @prev_revision_number INT
DECLARE @comments VARCHAR(1000)
DECLARE @holdString VARCHAR(10)
DECLARE @prevHoldString VARCHAR(10)
DECLARE @revsionNumberIncremented INT

DECLARE @lineItem_No VARCHAR(50)
DECLARE @prev_packageType VARCHAR(50)
DECLARE @prev_requestedDate VARCHAR(50)
DECLARE @prev_promisedDate VARCHAR(50)
DECLARE @prev_requestedQty INT
DECLARE @prev_receivedQty INT
DECLARE @prev_unitCost	NUMERIC(18,2)
DECLARE @prev_extendedCost NUMERIC(18,2)
DECLARE @prev_status VARCHAR(50)
DECLARE @prev_cancel INT
DECLARE @prev_financeHold INT
DECLARE @prev_operationsHold INT

DECLARE @prev_subTotal NUMERIC(18,2)
DECLARE @prev_freight NUMERIC(18,2)
DECLARE @prev_tradeDiscount NUMERIC(18,2)
DECLARE @prev_misc NUMERIC(18,2)
DECLARE @prev_tax NUMERIC(18,2)
DECLARE	@prev_total NUMERIC(18,2)

DECLARE @prev_taxType VARCHAR(50)

SELECT 	@prev_vendorQuote = VendorQuote, @prev_revision_number = ISNULL( PO_revision_No, 0 ), @prev_subTotal = ISNULL(PO_SubTotal, 0),
    @prev_freight = ISNULL( PO_Frieght, 0), @prev_tradeDiscount = ISNULL( PO_Trade_Discount, 0),@prev_misc = ISNULL( PO_Miscellaneous, 0),
    @prev_tax = ISNULL( PO_Tax, 0), @prev_total = ISNULL( PO_Total, 0 ), @prev_taxType = ISNULL( PO_Tax_Type, '-' ) FROM PurchaseOrder_Table WHERE PurchaseOrder_ID = @purchaseOrder_id

SET @comments = ''
SET @revsionNumberIncremented = 0
IF @isLineItemInfo = 1
BEGIN
    IF @prev_vendorQuote <> @vendorQuote
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        IF (RTRIM(LTRIM(@prev_vendorQuote))) = ''
        BEGIN
            SET @prev_vendorQuote = '-'
        END
        IF (RTRIM(LTRIM(@vendorQuote))) = ''
        BEGIN
            SET @vendorQuote = '-'
        END
        SET @comments = @comments + '<br> Vendor Quotation changed from: <b><i>' + @prev_vendorQuote + '</b></I> to <b><I>' + @vendorQuote  + '</b></I>'
    END
END
IF @isLineItemInfo = 0
BEGIN
    IF @poLineItem_ID = -1
    BEGIN
        SELECT @lineItem_No = max(lineItem_no) from purchaseorder_line_item_table where purchaseorder_id = @purchaseOrder_id
        SET @lineItem_No = @lineItem_No + 1
        SET @comments = @comments + '<br> LineItem '+ @lineItem_No + ' is added'
    END
    ELSE
    BEGIN
        SELECT  @lineItem_No = CONVERT( VARCHAR, LineItem_no),@prev_packageType = ISNULL( PackageType,''),@prev_requestedDate = CONVERT( VARCHAR, Requested_date, 101), @prev_promisedDate = ISNULL(CONVERT( VARCHAR, Promised_Date, 101), ''),
            @prev_requestedQty = Requested_Qty, @prev_receivedQty = Received_Qty, @prev_unitCost = UnitCost,
            @prev_extendedCost = Extended_Cost, @prev_status = Status, @prev_cancel = Is_cancel,
            @prev_financeHold = PO_Finance_Hold, @prev_operationsHold = PO_Operation_Hold FROM PurchaseOrder_Line_Item_Table WHERE POLineItem_Id = @poLineItem_ID


        /*PRINT ' @lineItem_No =  ' + @lineItem_No
        PRINT ' @prev_requestedDate =  ' + @requestedDate
        PRINT ' @prev_promisedDate =  ' + @promisedDate
        PRINT ' @prev_requestedQty =  ' + CONVERT( VARCHAR, @requestedQty )
        PRINT ' @prev_receivedQty =  ' + CONVERT( VARCHAR, @receivedQty )
        PRINT ' @prev_unitCost =  ' + CONVERT( VARCHAR, @unitCost )
        PRINT ' @prev_extendedCost =  ' + CONVERT( VARCHAR, @extendedCost )
        PRINT ' @prev_status =  ' + CONVERT( VARCHAR, @status )
        PRINT ' @prev_cancel =  ' + CONVERT( VARCHAR, @cancel )
        PRINT ' @prev_financeHold =  ' + CONVERT( VARCHAR, @financeHold )
        PRINT ' @prev_operationsHold =  ' + CONVERT( VARCHAR, @operationsHold )*/

        IF @prev_packageType <> @packageType
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            IF (RTRIM(LTRIM(@prev_packageType))) = ''
            BEGIN
                SET @prev_packageType = '-'
            END
            IF (RTRIM(LTRIM(@packageType))) = ''
            BEGIN
                SET @packageType = '-'
            END
            SET @comments = @comments + '<br> Package Type changed from: <b><I>' + @prev_packageType + '</b></I> to <b><I>' + @packageType + '</b></I> for line item ' + @lineItem_No
        END
        IF @prev_requestedDate <> @requestedDate
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Requested Date changed from: <b><I>' + @prev_requestedDate + '</b></I> to <b><I>' + @requestedDate + '</b></I> for line item ' + @lineItem_No
        END
        IF @prev_promisedDate <> @promisedDate
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            IF (RTRIM(LTRIM(@prev_promisedDate))) = ''
            BEGIN
                SET @prev_promisedDate = '-'
            END
            IF (RTRIM(LTRIM(@promisedDate))) = ''
            BEGIN
                SET @promisedDate = '-'
            END
            SET @comments = @comments + '<br> Promised Date changed from: <b><I>' + @prev_promisedDate + '</b></I> to <b><I>' + @promisedDate + '</b></I> for line item ' + @lineItem_No
        END


        IF @prev_requestedQty <> @requestedQty
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Requested Quantity is changed from: <b><i>' + CONVERT( VARCHAR, @prev_requestedQty) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @requestedQty) + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_receivedQty <> @receivedQty
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Received Quantity is: <b><I>' + CONVERT( VARCHAR, @receivedQty) + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_unitCost <> @unitCost
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Unit Cost changed from: <b><I>' + CONVERT( VARCHAR, @prev_unitCost) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @unitCost) + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_extendedCost <> @extendedCost
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Extended Cost changed from: <b><I>' + CONVERT( VARCHAR, @prev_extendedCost) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @extendedCost) + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_status <> @status
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Status changed from: <b><I>' + CONVERT( VARCHAR, @prev_status) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @status) + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_cancel <> @cancel
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br>LineItem ' + CONVERT( VARCHAR,@lineItem_No) +  ' has been cancelled'
        END

        IF @prev_financeHold <> @financeHold
        BEGIN
            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END

            IF @prev_financeHold = 0
            BEGIN
                SET @prevHoldString = 'Approved'
            END
            ELSE IF @prev_financeHold = -1
            BEGIN
                SET @prevHoldString = 'X'
            END
            ELSE
            BEGIN
                SET @prevHoldString = CONVERT( VARCHAR, @prev_financeHold )
            END

            IF @financeHold = 0
            BEGIN
                SET @holdString  = 'Approved'
            END
            ELSE IF @financeHold = -1
            BEGIN
                SET @holdString  = 'X'
            END
            ELSE
            BEGIN
                SET @holdString  = CONVERT( VARCHAR, @financeHold )
            END

            SET @comments = @comments + '<br> Finance Hold changed from: <b><I>' + @prevHoldString + '</b></I> to <b><I>' + @holdString + '</b></I> for line item ' + @lineItem_No
        END

        IF @prev_operationsHold <> @operationsHold
        BEGIN
            IF @prev_operationsHold = 0
            BEGIN
                SET @prevHoldString = 'Approved'
            END
            ELSE IF @prev_operationsHold = -1
            BEGIN
                SET @prevHoldString = 'X'
            END
            ELSE
            BEGIN
                SET @prevHoldString = CONVERT( VARCHAR, @prev_operationsHold )
            END

            IF @operationsHold = 0
            BEGIN
                SET @holdString  = 'Approved'
            END
            ELSE IF @operationsHold = -1
            BEGIN
                SET @holdString  = 'X'
            END
            ELSE
            BEGIN
                SET @holdString  = CONVERT( VARCHAR, @operationsHold )
            END

            IF @revsionNumberIncremented <> 1
            BEGIN
                SET @revisionNumber = @prev_revision_number + 1
                SET @revsionNumberIncremented = 1
            END
            SET @comments = @comments + '<br> Operations Hold changed from: <b><I>' + @prevHoldString + '</b></I> to <b><I>' + @holdString + '</b></I> for line item ' + @lineItem_No
        END
    END

END
IF @isLineItemInfo = 2
BEGIN
    IF  @prev_subTotal <> @subTotal
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> SubTotal changed from: <b><I>' + CONVERT( VARCHAR, @prev_subTotal) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @subTotal) + '</b></I>'
    END
    IF  @prev_freight <> @freight
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Freight changed from: <b><I>' + CONVERT( VARCHAR, @prev_freight) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @freight) + '</b></I>'
    END
    IF  @prev_tradeDiscount <> @tradeDiscount
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Trade Discount changed from: <b><I>' + CONVERT( VARCHAR, @prev_tradeDiscount) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @tradeDiscount) + '</b></I>'
    END
    IF  @prev_misc <> @misc
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Miscellaneous changed from: <b><I>' + CONVERT( VARCHAR, @prev_misc) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @misc) + '</b></I>'
    END
    IF  @prev_taxType <> @taxType
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Tax State changed from: <b><I>' + @prev_taxType + '</b></I> to <b><I>' + @taxType + '</b></I>'
    END
    IF  @prev_tax <> @tax
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Tax changed from: <b><I>' + CONVERT( VARCHAR, @prev_tax) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @tax) + '</b></I>'
    END
    IF  @prev_total <> @total
    BEGIN
        IF @revsionNumberIncremented <> 1
        BEGIN
            SET @revisionNumber = @prev_revision_number + 1
            SET @revsionNumberIncremented = 1
        END
        SET @comments = @comments + '<br> Total changed from: <b><I>' + CONVERT( VARCHAR, @prev_total) + '</b></I> to <b><I>' + CONVERT( VARCHAR, @total) + '</b></I>'
    END
END

-- PRINT ' @comments =  '	+ @comments

IF ( LEN( RTRIM( LTRIM ( @comments ) ) ) > 0 )
BEGIN
    INSERT INTO PurchaseOrder_Audit_Trail( PurchaseOrder_ID, [User_Id], Actions, TimeStamp ) VALUES( @purchaseOrder_id, @user_id, @comments, CURRENT_TIMESTAMP )
END

--SELECT @revisionNumber
--RETURN
END